package com.example.service.impl;

import ch.qos.logback.classic.Logger;
import com.example.entity.CustomReport;
import com.example.mapp.CustomReportMapp;
import com.example.mapp.JgCustomCountMapp;
import com.example.service.JgCustomCountService;
import com.example.util.ResultUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 监管场所自定义查询导出
 */
@Service
public class JgCustomCountServiceImpl implements JgCustomCountService {

	private final static Logger logger = (Logger) LoggerFactory.getLogger(JgCustomCountServiceImpl.class);

	@Autowired
	private JgCustomCountMapp jgCustomCountMapp;
	// 自定义数据报表
	@Autowired
	private CustomReportMapp customReportMapp;

	@Autowired
	private Environment environment;

	/**
	 * 查询数据信息
	 * @param map
	 * @return
	 */
	@Override
	public Map<String, Object> findPortInfo(Map map) {
		String message = "查询成功";
		List<Map<String,Object>> resultList = new ArrayList<>();
		try {
			if (map != null && map.size() > 0) {
				// 获取主键Id
				CustomReport customReport = new CustomReport();
				customReport.setId( Long.parseLong(map.get("id")+""));
				List<CustomReport> list = customReportMapp.findList(customReport);
				if (list != null && list.size() > 0) {
					CustomReport report = list.get(0);

					map.put("sql", report.getSqlSentence());
					// 通过id查询SQL
					resultList = jgCustomCountMapp.findPortInfo(map);
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			message = "查询失败";
			return ResultUtil.returnError(message + e.getMessage(), null);
		}
		return ResultUtil.returnSuccess(message, resultList);
	}

	//动态创建导出表格
	@Override
	public Map<String, Object> export(Map map) {
		//自定义查询
		String message = "查询成功";
		CustomReport report = null;
		List<Map<String,Object>> resultList = new ArrayList<>();
		List<CustomReport> list = null;
		try {
			if (map != null && map.size() > 0) {
				// 获取主键Id
				CustomReport customReport = new CustomReport();
				customReport.setId(Long.parseLong(map.get("id")+""));
				list = customReportMapp.findList(customReport);
				if (list != null && list.size() > 0) {
					report = list.get(0);
					map.put("sql", report.getSqlSentence());
					// 通过id查询SQL
					resultList = jgCustomCountMapp.findPortInfo(map);
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
			message = "查询失败";
			return ResultUtil.returnError(message + e.getMessage(), null);
		}
		//如果查询结果不为空，则创建表格，否则不创建
		if (resultList!=null && resultList.size()>0 && resultList.get(0).size() > 0) {
			//根据查询结果建表
			Map<String, Object> result = new HashMap<>();
			// 1.获取保存路径，不存在则创建
			String tableName = report.getReportName();//表名；
			int colNum = resultList.get(0).size(); //列数
			String path = environment.getProperty("upload", String.class);

			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			String dateString = sdf.format(new Date());
			if (path.endsWith(File.separator)) {
				path = path + dateString + File.separator;
			} else {
				path = path + File.separator + dateString + File.separator;
			}

			File file1 = new File(path);
			if (!file1.exists()) {
				boolean flag = file1.mkdirs();
			}
			// 重新生成文件名，防止中文乱码
			String fileName = UUID.randomUUID() + ".xlsx";
			path = path + fileName;
			result.put("fileUrl", dateString + File.separator + fileName);
			result.put("fileName", tableName + ".xlsx");
			FileOutputStream fileOutStream = null;
			try {
				// 2.创建workbook和sheet
				fileOutStream = new FileOutputStream(path);
				XSSFWorkbook wb = new XSSFWorkbook();
				XSSFDataFormat dataFormat = wb.createDataFormat();
				XSSFSheet sheet = wb.createSheet(tableName);
				//3.自动调整列的宽度
				for (int i = 0; i <resultList.get(0).size() ; i++) {
					/*sheet.autoSizeColumn((short)i);*/
					sheet.setColumnWidth(i ,20*256 );
				}
				// 4.合并单元格以及设置宽度
				mergeRegionAndSetWidth(sheet,colNum);
				// 5.绘制表单
				// 5.1 样式
				XSSFCellStyle style1 = wb.createCellStyle();
				style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
				style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
				style1.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM); // 中等边线


				XSSFFont font1 = wb.createFont();
				font1.setFontName("方正小标宋简体");
				font1.setFontHeightInPoints((short) 14);
				style1.setFont(font1);
				XSSFCellStyle style2 = wb.createCellStyle();
				style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
				style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
				style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 中等边线
				style2.setBorderTop(XSSFCellStyle.BORDER_THIN); // 中等边线
				style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 中等边线
				style2.setBorderRight(XSSFCellStyle.BORDER_THIN); // 中等边线
				style2.setWrapText(true);
				XSSFFont font2 = wb.createFont();
				font2.setFontName("宋体");
				font2.setFontHeightInPoints((short) 10.5);
				style2.setFont(font2);
				XSSFCellStyle style3 = wb.createCellStyle();
				style3.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居左
				style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
				style3.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 中等边线
				style3.setBorderTop(XSSFCellStyle.BORDER_THIN); // 中等边线
				style3.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 中等边线
				style3.setBorderRight(XSSFCellStyle.BORDER_THIN); // 中等边线
				style3.setWrapText(true);
				style3.setFont(font2);
				Set set0 = resultList.get(0).keySet();
				//第一行，表名
				String reportName = list.get(0).getReportName();
				XSSFRow row0 = sheet.createRow(0);
				row0.setHeightInPoints(20);
				for (int i = 0; i <resultList.get(0).size() ; i++) {
					XSSFCell cell0i = row0.createCell(i);
					cell0i.setCellValue(reportName);
					cell0i.setCellStyle(style1);
				}
				//第二行,添加列名
				XSSFRow row1 = sheet.createRow(1);
				row1.setHeight((short) (25 * 20));



				XSSFCell cell0 = row1.createCell(0);
				cell0.setCellValue("序号");
				cell0.setCellStyle(style3);
				int num = 0;
				for (Object object : set0) {
					String columnName = (String) object;
					XSSFCell celli = row1.createCell(num + 1);
					celli.setCellValue(columnName);
					celli.setCellStyle(style3);
					num++;
				}

				//循环创建表格,将查询信息放入表格
				for (int i = 0; i < resultList.size(); i++) {
					XSSFRow row = sheet.createRow(i + 2);
					row.setHeightInPoints(22f);
					Map<String, Object> map1 = resultList.get(i);
					Set<String> set = map1.keySet();
					XSSFCell celli = row.createCell(0);
					celli.setCellValue(Integer.toString((i + 1)));//创建表格序号
					celli.setCellStyle(style3);
					int j = 0;
					Iterator it = set.iterator();
					while (it.hasNext()) {
						Object value = map1.get(it.next());
						XSSFCell cell = row.createCell(j + 1);
						cell.setCellStyle(style3);
						if (value instanceof Date ||
								value instanceof Calendar||
								value instanceof String ||
								value instanceof Float ||
								value instanceof Double ||
								value instanceof Integer ||
								value instanceof BigDecimal ) {
							if (value instanceof Date) {
								SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
								String format = simpleDateFormat.format((Date)value);
								cell.setCellValue(format);
							}
							if (value instanceof String) {
								cell.setCellValue((String) value);
							}
							if (value instanceof Float || value instanceof Double) {
								cell.setCellValue((Double) value);
							}
							if (value instanceof Integer) {
								cell.setCellValue((Integer) value);
							}
							if (value instanceof Calendar) {
								cell.setCellValue((Calendar) value);
							}
							if (value instanceof BigDecimal) {
								BigDecimal value1 = (BigDecimal) value;
								String value2 = value1.toString();
								cell.setCellValue(value2);
							}
						} else {
							cell.setCellValue("数据类型异常");
						}
						j++;
					}
				}
				//
				wb.write(fileOutStream);
			} catch (Exception e) {
				logger.error(e.getMessage());
				return ResultUtil.returnError(e.getMessage(), null);
			}finally {
				if(fileOutStream != null){
					try{
						fileOutStream.flush();
						fileOutStream.close();
					}catch (Exception e){
						e.printStackTrace();
					}
				}
			}
			return ResultUtil.returnSuccess(message, result);
		}
		return ResultUtil.returnSuccess(message, null);
	}
	//合并单元格
	private void mergeRegionAndSetWidth(XSSFSheet sheet,int colNum) {
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,colNum));
	}
}
